/* Program name: auto_debt.sas*/
/* Objective: create an estimate of vehicle-related debt and student loan debt.;
/* This code requires the clo variable, which is created in clean_tradeline01.sas, so cannot 
  be run on the raw tradeline files*/
/* Note: HARDCODED DATES BELOW*/

libname in '/data';
libname scorein  '/data';
%include "/data/tu_formats.sas";
%include "/data/06macros.sas";

data temp (keep = teditseq terms hicredit acctype loantypt loantype int acctbal  ecoa crdtlim cll auto_: /*trans_:*/ mortgage_: dtveri student_: ccard_: cctot_: install_: lcred_: unsec_: secgoods_: lease_: dtclose dtopen paypat mop rename=(dtclose=dtclo));
  set in.cleantrade01;

  if clo eq 1 or dispcd in ('IA','INA','FTS','FTB','ETS','ETB','ETI') then coo = 1;	/*closed, terminated, inactive, etc*/
  else coo = 0;

  if dtclose gt 0 or coo eq 1 then cll = 1;
  else if acctype in ('I', 'M') and acctbal le 0 and mop eq 1 then cll = 1;             /*installment, mortgage*/
  else cll = 0;

  if acctbal lt 0 then acctbal = .;

/************************ADDED CODE 5/2019*****************/


/* installment loans */
  if loantypt in('IS') then do;
     loantype = "INSTALL"; 
     install_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     install_hist_ontime = paymop1_24;
     install_hist_3060 = pay3059_24;
     install_hist_6090 = pay6089_24;
     install_hist_90120 = pay90119_24;
     install_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then install_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200007 then do;
	      if acctbal eq 0 then install_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(install);
	end;
     end;
  end;

/* line of credit */
  if loantypt in('LC') then do;
     loantype = "LCRED"; 
     lcred_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lcred_hist_ontime = paymop1_24;
     lcred_hist_3060 = pay3059_24;
     lcred_hist_6090 = pay6089_24;
     lcred_hist_90120 = pay90119_24;
     lcred_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lcred_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200007 then do;
	      if acctbal eq 0 then lcred_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lcred);
	end;
     end;
  end;


/* Unsecured */
  if loantypt in('US') then do;
     loantype = "UNSEC"; 
     unsec_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     unsec_hist_ontime = paymop1_24;
     unsec_hist_3060 = pay3059_24;
     unsec_hist_6090 = pay6089_24;
     unsec_hist_90120 = pay90119_24;
     unsec_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then unsec_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200007 then do;
	      if acctbal eq 0 then unsec_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(unsec);
	end;
     end;
  end;

/* Household goods secured & collateral */
  if loantypt in('SO','SH') then do;
     loantype = "SECGOODS"; 
     secgoods_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     secgoods_hist_ontime = paymop1_24;
     secgoods_hist_3060 = pay3059_24;
     secgoods_hist_6090 = pay6089_24;
     secgoods_hist_90120 = pay90119_24;
     secgoods_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then secgoods_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200007 then do;
	      if acctbal eq 0 then secgoods_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(secgoods);
	end;
     end;
  end;


/* lease */
  if loantypt in('LE') then do;
     loantype = "LEASE"; 
     lease_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lease_hist_ontime = paymop1_24;
     lease_hist_3060 = pay3059_24;
     lease_hist_6090 = pay6089_24;
     lease_hist_90120 = pay90119_24;
     lease_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lease_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200007 then do;
	      if acctbal eq 0 then lease_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lease);
	end;
     end;
  end;


/************************STOP ADDED CODE 5/2019*****************/


  /* total auto_loans */
  if loantypt in('AU','AL','AR','AT','CA','MB','MT','RV') then do;
     loantype = "AUTO"; 
     auto_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     auto_hist_ontime = paymop1_24;
     auto_hist_3060 = pay3059_24;
     auto_hist_6090 = pay6089_24;
     auto_hist_90120 = pay90119_24;
     auto_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then auto_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200007 then do;
	      if acctbal eq 0 then auto_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(auto);
	end;
     end;
  end;

  * total student debt;
  if loantypt ='ST' then do;
     loantype = "STUD"; 
     /*if dtopen ge 199901 then*/ student_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline01.sas*/
     student_hist_ontime = paymop1_24;
     student_hist_3060 = pay3059_24;
     student_hist_6090 = pay6089_24;
     student_hist_90120 = pay90119_24;
     student_hist_120plus = pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then student_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200007 then do;
       if acctbal eq 0 then student_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(student);
     	end;
     end;
  end;



  /* total mortgage debt*/
  if loantypt in ('CV', 'FH', 'FI', 'FR', 'HE', 'HI', 'PI', 'RE', 'RL', 'RM', 'RT', 'SM', 'VA', 'VM','UK',' ') and acctype eq 'M' then do;
     loantype = "MORT";
     mortgage_open = 1;

     mortgage_hist_ontime = paymop1_24;
     mortgage_hist_3060 = pay3059_24;
     mortgage_hist_6090 = pay6089_24;
     mortgage_hist_90120 = pay90119_24;
     mortgage_hist_120plus = pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then mortgage_clo = 1;

     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 then do;
      if acctbal eq 0 then mortgage_clo = 1; 
        if acctbal gt 0 then do;
	      %recentcount(mortgage);
     	end;
     end;
  end;


  /* credit card debt */
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many people have no credit cards*/
  if loantypt in('CC') then do;
  loantype = "CCAR";
     if ecoa = "I" then do;
     	ccard_indiv_open = 1;
     	ccard_indiv_hist_ontime = paymop1_24;
	ccard_indiv_hist_3060 = pay3059_24;
     	ccard_indiv_hist_6090 = pay6089_24;
     	ccard_indiv_hist_90120 = pay90119_24;
     	ccard_indiv_hist_120plus = pay120up_24;
     end;

     if ecoa = "C" then do;
     ccard_joint_open = 1;
	ccard_joint_hist_ontime = paymop1_24;
	ccard_joint_hist_3060 = pay3059_24;
     	ccard_joint_hist_6090 = pay6089_24;
     	ccard_joint_hist_90120 = pay90119_24;
     	ccard_joint_hist_120plus = pay120up_24;
     end;

     if ecoa in ("M", "S", "C") then do;
     ccard_cosign_open = 1;
	ccard_cosign_hist_ontime = paymop1_24;
	ccard_cosign_hist_3060 = pay3059_24;
     	ccard_cosign_hist_6090 = pay6089_24;
     	ccard_cosign_hist_90120 = pay90119_24;
     	ccard_cosign_hist_120plus = pay120up_24;
     end;

     if ecoa = "A" then do;
     	ccard_notliab_open = 1;
	ccard_notliab_hist_ontime = paymop1_24;
	ccard_notliab_hist_3060 = pay3059_24;
     	ccard_notliab_hist_6090 = pay6089_24;
     	ccard_notliab_hist_90120 = pay90119_24;
     	ccard_notliab_hist_120plus = pay120up_24;
     end;

     if ecoa in ("A", "P") then do;
     ccard_maybeliab_open = 1;
	ccard_maybeliab_hist_ontime = paymop1_24;
	ccard_maybeliab_hist_3060 = pay3059_24;
     	ccard_maybeliab_hist_6090 = pay6089_24;
     	ccard_maybeliab_hist_90120 = pay90119_24;
     	ccard_maybeliab_hist_120plus = pay120up_24;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then ccard_indiv_clo = 1;
	   /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa = "I" then do;
     	%recentcount(ccard_indiv);
     end;

     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then ccard_joint_clo = 1;
	    /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa = "C" then do;
 	%recentcount(ccard_joint);
     end;

     
     * cosigner;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then ccard_cosign_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa in ("M", "S", "C") then do;
        %recentcount(ccard_cosign);
     end; 

     * not liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then ccard_notliab_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa in ("A") then do;
        %recentcount(ccard_notliab);
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then ccard_maybeliab_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa in ("A", "P") then do;
        %recentcount(ccard_maybeliab); 
     end; 

  end;
 
  /* credit card total debt (secured + unsecured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC','SC') then do;
      
              
      if ecoa = "I" then do;
     	cctot_indiv_open = 1;
     	cctot_indiv_hist_ontime = paymop1_24;
	cctot_indiv_hist_3060 = pay3059_24;
     	cctot_indiv_hist_6090 = pay6089_24;
     	cctot_indiv_hist_90120 = pay90119_24;
     	cctot_indiv_hist_120plus = pay120up_24;
     end;

     if ecoa = "C" then do;
     	cctot_joint_open = 1;
	cctot_joint_hist_ontime = paymop1_24;
	cctot_joint_hist_3060 = pay3059_24;
     	cctot_joint_hist_6090 = pay6089_24;
     	cctot_joint_hist_90120 = pay90119_24;
     	cctot_joint_hist_120plus = pay120up_24;
     end;

     if ecoa in ("M", "S", "C") then do;
     	 cctot_cosign_open = 1;
	cctot_cosign_hist_ontime = paymop1_24;
	cctot_cosign_hist_3060 = pay3059_24;
     	cctot_cosign_hist_6090 = pay6089_24;
     	cctot_cosign_hist_90120 = pay90119_24;
     	cctot_cosign_hist_120plus = pay120up_24;
     end;

     if ecoa = "A" then do;
     cctot_notliab_open = 1;
	cctot_notliab_hist_ontime = paymop1_24;
	cctot_notliab_hist_3060 = pay3059_24;
     	cctot_notliab_hist_6090 = pay6089_24;
     	cctot_notliab_hist_90120 = pay90119_24;
     	cctot_notliab_hist_120plus = pay120up_24;
     end;

     if ecoa in ("A", "P") then do;
     cctot_maybeliab_open = 1;
	cctot_maybeliab_hist_ontime = paymop1_24;
	cctot_maybeliab_hist_3060 = pay3059_24;
     	cctot_maybeliab_hist_6090 = pay6089_24;
     	cctot_maybeliab_hist_90120 = pay90119_24;
     	cctot_maybeliab_hist_120plus = pay120up_24;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then cctot_indiv_clo = 1;
	  
     /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa = "I" then do;
     	%recentcount(cctot_indiv);
     end;

     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then  cctot_joint_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa = "C" then do;
	%recentcount(cctot_joint);
     end;
 
     * cosigner;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then cctot_cosign_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa in ("M", "S", "C") then do;
        %recentcount(cctot_cosign);
     end; 

     * not liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then cctot_notliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa in ("A") then do;
        %recentcount(cctot_notliab);
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then cctot_maybeliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200007 and ecoa in ("A", "P") then do;
        %recentcount(cctot_maybeliab);
     end; 
  end;
run;


/*select variables that we want to get sum*/
proc sql;
     select name
     into :sumlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_debt' or name ? '_num' or name like '%_ontime' or name ? '_lim'
     or name like '%3060' or name like '%6090' or name like '%90120' 
     or name like '%120plus' or name like '%_worse'
     or name ? '_clo' or name ? '_open');
     quit;
/*select lot variables that we want to get min (choose earliest date)*/
proc sql;
     select name 
     into :minlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_lot' or name ? '_f120');
     quit;
/*collapse the dataset to total accounts of debt per person*/
 proc means data = temp noprint;
  by teditseq;
  var install: lcred: unsec: secgoods: lease: auto: trans: student: mortgage: ccard: cctot:;
  output out=rev_install_type01 min(&minlist)= sum(&sumlist)=;
run;

data rev_install_type01;
    set rev_install_type01;
    drop _type_ _freq_;

    ccard_indiv_joint_debt=sum(ccard_indiv_debt,.5*ccard_cosign_debt);
    cctot_indiv_joint_debt=sum(cctot_indiv_debt,.5*cctot_cosign_debt);

ccard_indiv_joint_lim=sum(ccard_indiv_lim,.5*ccard_cosign_lim);
    cctot_indiv_joint_lim=sum(cctot_indiv_lim,.5*cctot_cosign_lim);

run;

 			

proc sort data=rev_install_type01; by teditseq; run;
proc sort data=temp; by teditseq; run;


data temp_new;


 merge temp (in = a keep= teditseq dtopen dtveri dtclo terms hicredit install_clo install_open lcred_clo lcred_open unsec_clo unsec_open secgoods_clo secgoods_open lease_clo lease_open 
student_clo student_open mortgage_clo mortgage_open auto_clo auto_open loantype student_debt int crdtlim acctbal ecoa
                    rename=(student_debt=student_debt_tl student_clo=student_clo_tl 
student_open=student_open_tl 
mortgage_clo=mortgage_clo_tl 
mortgage_open=mortgage_open_tl
install_clo=install_clo_tl 
install_open = install_open_tl
lcred_clo = lcred_clo_tl
lcred_open = lcred_open_tl 
unsec_clo = unsec_clo_tl 
unsec_open = unsec_open_tl 
secgoods_clo = secgoods_clo_tl
secgoods_open = secgoods_open_tl
lease_clo = lease_clo_tl
lease_open = lease_open_tl 
terms=terms_tl 
hicredit=hicredit_tl 
auto_clo=auto_clo_tl 
auto_open=auto_open_tl 
crdtlim=crdtlim_tl 
acctbal=acctbal_tl 
ecoa=ecoa_tl))



 rev_install_type01 (in = b);
 by teditseq;      
if a; 
run;


/* pull roll up & locational variables from SCORE dataset */
/* we rename the variables to chVARNAME in order to convert them from character to numeric*/
/* we do this because the variables in 03-08 are numeric, and we want them all formatted the same*/
%let renamelist=CBSA=chCBSA COUNTY=chCOUNTY LAT=chLAT LONG=chLONG STATE=chSTATE TRACT=chTRACT ZIP=ZIP01;

data roll_up01(drop=AT01 ch:);
     set scorein.score01 (keep= teditseq RE28 RE34 AT33 AT28 ON33 MT33 IN33 RE33 G041-G045 G057-G071 G082-G087 
     	 		 G104 G093-G094 G096 S059 S063-S064
			 CBSA COUNTY LAT LONG STATE TRACT ZIP score AT01 rename=(&renamelist));
     rename score=tr_am;
     /*if AT01 ne 0;	 we want to include obs with 0 tradelines from score per conversation
     	      	 	 with alvaro - AN 06.20.13 */
     CBSA=input(chCBSA,8.);
     COUNTY=input(chCOUNTY,8.);
     LAT=input(chLAT,8.);
     LONG=input(chLONG,8.);
     STATE=input(chSTATE,8.);
     TRACT=input(substr(chTRACT,1,4),8.);
     SUFFIX=input(substr(chTRACT,5,2),8.);     
run;


proc contents data=roll_up01;
run;

data in.roll_trades01(rename=(teditseq=teditseq01));
     merge roll_up01 
           temp_new ;
     by teditseq; /* the merge should be uncondtional on in=a or in=b*/  
     
run;

proc contents data=in.roll_trades01;
run;

proc print data=in.roll_trades01(obs=30);
run;




